/**
 * Created by ZHOUSH on 11/27/2015.
 */
var express = require('express');
var bodyParser = require('body-parser');
var multer = require('multer');
var upload = multer({dest: 'api/file/upload'});
var connstr = require('../tool/connstr');
var oracledb = require('oracledb');
var connutil = require('../tool/connection');
var Promise = require('promise');
var filemap = require('../tool/filemap');
var csvmap = require('../tool/csvmap');
var crypto = require('crypto');
var ptocsv = Promise.denodeify(require('csv').stringify);
var pwrite = Promise.denodeify(require('fs').writeFile);
var pread = Promise.denodeify(require('fs').readFile);
var prename = Promise.denodeify(require('fs').rename);
var punlink = Promise.denodeify(require('fs').unlink);
var app = express();
var conns = {};

var prefix = function (item) {
    return item.account + '@' + item.system + '_' + item.system;
};

exports.run = function () {

    var sql_upload = multer({
        dest: 'server/file/tmp'
    });

    app.get('/', function (req, res) {
        res.sendFile(process.cwd() + '/public/index.html');
    });

    app.use(express.static(process.cwd() + '/public'));
    app.use(express.static(process.cwd() + '/server/file'));
    app.use(bodyParser.urlencoded({extended: true}));
    app.use(bodyParser.json());

    app.get('/download/sql', function (req, res) {
        var param = req.query;
        if (param['hash']) {
            filemap.find(param['hash']).then(function (item) {
                if (param['data']) {
                    var org = item.original.split('.'), fn = '';
                    if (org.length == 1) fn = org[0];
                    else {
                        fn = org.slice(0, org.length - 1).join('.')
                    }
                    res.attachment(fn + '_data.json');
                    res.sendFile(__dirname + '/file/data/' + item.filename + '_DATA', function (err) {
                        console.log(err);
                    });
                } else {
                    res.attachment(item.original);
                    res.sendFile(__dirname + '/file/sql/' + item.filename, function (err) {
                        console.log(err);
                    });
                }
            })
        }
    });

    app.get('/api/connections/all', function (req, res) {
        var all = connstr.all();
        res.json(all.map(function (item) {
            if (conns[prefix(item)]) {
                item.key = prefix(item);
                item.status = 'CONNECTED';
            } else {
                item.status = 'CLOSED';
            }
            return item;
        }));
    });

    app.post('/api/file/list', function (req, res) {
        var param = req.body;
        var filter = function (item) {
            var ret = true;
            if (param.original) ret &= ([].concat(param.original).indexOf(item.original) >= 0);
            if (param.filename) ret &= ([].concat(param.filename).indexOf(item.filename) >= 0);
            if (param.hash) ret &= ([].concat(param.hash).indexOf(item.hash) >= 0);
            if (param.from) ret &= (item.time >= new Date(param.from).getTime());
            if (param.to) ret &= (item.time <= new Date(param.to).getTime());
            return ret;
        };
        filemap.query(filter).then(function (all) {
            res.json(all);
        })
    });

    app.post('/api/file/upload/sql', sql_upload.any(), function (req, res) {
        Promise.all(req.files.map(function (item) {
            var hash = '';
            return pread('server/file/tmp/' + item.filename).then(function (data) {
                hash = crypto.createHash('md5').update(data).digest('hex');
                return filemap.find(hash);
            }).then(function (found) {
                if (found) {
                    return filemap.update(hash).then(function (final) {
                        return punlink('server/file/tmp/' + item.filename).then(function () {
                            return Promise.resolve(final);
                        });
                    });
                } else {
                    return prename('server/file/tmp/' + item.filename, 'server/file/sql/' + item.filename).then(function () {
                        return filemap.insert(item.originalname, item.filename, hash).then(function (final) {
                            return Promise.resolve(final);
                        }, function (err) {
                            return {
                                error: 'ERR_FILE_MAP_ERROR',
                                params: [err.message]
                            };
                        });
                    });
                }
            });
        })).then(function (items) {
            res.json(items);
            res.end();
        });
    });

    app.post('/api/query/file', function (req, res) {
        var query = req.body;
        var conn = conns[query.conn];
        var hash = query.hash;
        if (!conn) {
            res.status(500).json({
                code: 'ERR_NO_CONNECTION',
                params: [query.conn]
            });
        } else {
            filemap.find(hash).then(function (item) {
                if (item) {
                    return pread('server/file/sql/' + item.filename, 'utf-8').then(function (data) {
                        var sql = data;
                        sql = sql.trim();
                        if (sql[sql.length - 1] == ';') {
                            sql = sql.substring(0, sql.length - 1);
                        }
                        return connutil.execute(conn, sql).then(function (data) {
                            var filename = item.filename + '_DATA';
                            var str = JSON.stringify(data);
                            return pwrite('server/file/data/' + filename, str).then(function () {
                                hash = crypto.createHash('md5').update(str).digest('hex');
                                return {
                                    original: item.original,
                                    filename: filename,
                                    hash: hash
                                }
                            }).catch(function (ex) {
                                res.status(500).json({
                                    code: 'ERR_FILE_WRITE_ERR',
                                    params: ex
                                });
                            })
                        }).catch(function (ex) {
                            res.status(500).json({
                                code: 'ERR_DB_EXCEPTION',
                                params: ex
                            });
                        });
                    })
                } else {
                    res.status(500).json({
                        code: 'ERR_NO_FILE',
                        params: [item.original]
                    });
                    return null;
                }
            }).then(function (ret) {
                if (ret) {
                    res.json(ret);
                }
                res.end();
            })
        }
    });

    var midQuery = function (parseSql) {
        return function (req, res) {
            var query = req.body;
            var conn = conns[query.conn];
            var type = query.type, name = query.name;
            if (!conn) {
                res.status(500).json({
                    code: 'ERR_NO_CONNECTION',
                    params: [query.conn]
                })
            } else {
                connutil.execute(conn, parseSql(query.sql)).then(function (data) {
                    if (type && name) {
                        res.attachment(name);
                        if (type == 'csv') {
                            ptocsv(data, {
                                header: true
                            }).then(function (str) {
                                res.send(str);
                            });
                        } else if (type == 'json') {
                            res.send(JSON.stringify(data));
                            res.end();
                        }
                    } else {
                        res.json({
                            conn: query.conn,
                            data: data
                        });
                        res.end();
                    }
                }).catch(function (err) {
                    res.status(500).json({
                        code: 'ERR_DB_EXCEPTION',
                        params: [err.message]
                    })
                })
            }
        }
    };

    app.post('/api/query/count', midQuery(function (sql) {
        sql = sql.trim();
        if (sql[sql.length - 1] == ';') {
            sql = sql.substring(0, sql.length - 1);
        }
        console.log(sql);
        return "SELECT COUNT(1) Count FROM (" + sql + ")";
    }));

    app.post('/api/query/query', midQuery(function (sql) {
        sql = sql.trim();
        if (sql[sql.length - 1] == ';') {
            sql = sql.substring(0, sql.length - 1);
        }
        console.log(sql);
        return sql;
    }));

    app.get('/api/connections/list', function (req, res) {
        var list = [];
        for (var prop in conns) {
            if (conns.hasOwnProperty(prop)) {
                list.push(prop);
            }
        }
        res.json(list);
    });

    app.post('/api/connections/connect', function (req, res) {
        var constr = connstr.use(req.body.system, req.body.db, req.body.account);
        var key = prefix(req.body);
        if (conns[key]) {
            res.end(key);
        }
        oracledb.getConnection(constr, function (err, conn) {
            if (err) {
                res.status(500).send(err.toString());
            } else {
                conns[key] = conn;
                res.end(key);
            }
        })
    });

    app.post('/api/connections/close', function (req, res) {
        var prefix = '' + req.body.connection;
        if (conns[prefix]) {
            conns[prefix].release(function (err) {
                if (err) {
                    res.status(500).send(err.toString());
                }
                delete conns[prefix];
                res.end(prefix);
            });
        } else {
            res.end(prefix);
        }
    });

    app.listen(1025);
};